package com.situ.car.dao.Impl;

import com.situ.car.dao.IAdminDao;
import com.situ.car.pojo.entity.Admin;
import com.situ.car.pojo.query.AdminQuery;
import com.situ.car.pojo.vo.AdminVo;
import com.situ.car.util.JDBCUtils;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.ObjectUtils;

import java.util.ArrayList;
import java.util.List;

public class AdminDaoImpl implements IAdminDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
    @Override
    public Admin login(String account, String password) {
        try {
            return template.queryForObject("select id, username, account, password, avatar, status from administrator where account=? and password=?",
                    new BeanPropertyRowMapper<>(Admin.class), account, password);
        } catch (EmptyResultDataAccessException ex) {
            return null;
        }
    }

    @Override
    public List<AdminVo> selectByPage(AdminQuery adminQuery) {
        String sql = "SELECT a.id,a.username,a.account,a.password,a.avatar,a.gender,a.age,a.email,a.status,ar.role_id AS roleId,r.role_name AS roleName,\n" +
                "GROUP_CONCAT(p.permission_name) AS permissionName\n" +
                "FROM administrator AS a\n" +
                "JOIN administrator_role AS ar ON a.id = ar.administrator_id\n" +
                "JOIN role AS r ON ar.role_id = r.id\n" +
                "LEFT JOIN role_permission AS rp ON r.id = rp.role_id\n" +
                "LEFT JOIN permission AS p ON rp.permission_id = p.id where 1=1";
        List<Object> queryList = new ArrayList<>();
        String queryUsername = adminQuery.getUsername();
        if (!ObjectUtils.isEmpty(queryUsername)) {
            sql += " and a.username like ?";
            queryList.add("%" + queryUsername + "%");
        }
        String queryAccount = adminQuery.getAccount();
        if (!ObjectUtils.isEmpty(queryAccount)) {
            sql += " and a.account like ?";
            queryList.add("%" + queryAccount + "%");
        }
        Integer queryRoleId = adminQuery.getRoleId();
        if (!ObjectUtils.isEmpty(queryRoleId)) {
            sql += " and r.id=?";
            queryList.add(queryRoleId);
        }
        sql += " GROUP BY a.id, a.username, a.account, a.password, a.avatar, a.gender, a.age, a.email, a.status, ar.role_id, r.role_name limit ?,?";
        int offset = (adminQuery.getPage() - 1) * adminQuery.getLimit();
        queryList.add(offset);
        queryList.add(adminQuery.getLimit());
        List<AdminVo> list = template.query(sql, new BeanPropertyRowMapper<>(AdminVo.class), queryList.toArray());
        return list;
    }

    @Override
    public int selectTotalCount(AdminQuery adminQuery) {
        String sql = "SELECT COUNT(*) \n" +
                "FROM administrator AS a\n" +
                "LEFT JOIN administrator_role AS ar ON a.id = ar.administrator_id\n" +
                "LEFT JOIN role AS r ON ar.role_id = r.id where 1=1";
        List<Object> queryList = new ArrayList<>();
        String queryUsername = adminQuery.getUsername();
        if (!ObjectUtils.isEmpty(queryUsername)) {
            sql += " and a.username like ?";
            queryList.add("%" + queryUsername + "%");
        }
        String queryAccount = adminQuery.getAccount();
        if (!ObjectUtils.isEmpty(queryAccount)) {
            sql += " and a.account like ?";
            queryList.add("%" + queryAccount + "%");
        }
        Integer queryRoleId = adminQuery.getRoleId();
        if (!ObjectUtils.isEmpty(queryRoleId)) {
            sql += " and r.id=?";
            queryList.add(queryRoleId);
        }
        Integer count = template.queryForObject(sql, Integer.class, queryList.toArray());
        return count;
    }

    @Override
    public void add(AdminVo adminVo) {
        template.update("insert IGNORE into administrator (username, account, password, avatar, gender, age, email) VALUES (?,?,?,?,?,?,?)",
                adminVo.getUsername(), adminVo.getAccount(), adminVo.getPassword(), adminVo.getAvatar(), adminVo.getGender(), adminVo.getAge(), adminVo.getEmail());
    }

    @Override
    public void deleteByIdAR(Integer id) {
        template.update("delete from administrator_role where administrator_id=?", id);
    }

    @Override
    public void updateAR(AdminVo adminVo) {
        template.update("UPDATE administrator_role SET role_id = ? WHERE administrator_id = ?;",
                adminVo.getRoleId(), adminVo.getId());
    }

    @Override
    public AdminVo selectRole(String account) {
        return template.queryForObject("SELECT role.id AS roleId\n" +
                "FROM administrator\n" +
                "JOIN administrator_role ON administrator.id = administrator_role.administrator_id\n" +
                "JOIN role ON administrator_role.role_id = role.id\n" +
                "WHERE administrator.account = ?", new BeanPropertyRowMapper<>(AdminVo.class), account);
    }

    @Override
    public Integer selectByAccount(AdminVo adminVo) {
        return template.queryForObject("select count(*) from administrator where account=?", Integer.class,adminVo.getAccount());
    }

    @Override
    public Admin selectByInfoId(Integer id) {
        return template.queryForObject("SELECT * FROM administrator where id=?", new BeanPropertyRowMapper<>(Admin.class), id);
    }

    @Override
    public int updateInfo(Admin admin) {
        try {
            return template.update("update administrator set username=?, account=?, avatar=?, gender=?, age=?, email=? where id=?",admin.getUsername(), admin.getAccount(), admin.getAvatar(), admin.getGender(), admin.getAge(), admin.getEmail(), admin.getId());
        } catch (DuplicateKeyException e) {
            return 0;
        }
    }

    @Override
    public Admin selectAccount(Integer id) {
        return template.queryForObject("SELECT id,account FROM administrator where id=?", new BeanPropertyRowMapper<>(Admin.class), id);
    }

    @Override
    public Admin selectPassword(Integer id) {
        return template.queryForObject("SELECT password FROM administrator where id=?", new BeanPropertyRowMapper<>(Admin.class), id);
    }

    @Override
    public void updatePassword(Integer id, String newPassword) {
        template.update("update administrator set password=? where id=?", newPassword, id);
    }

    @Override
    public AdminVo selectById(Integer id) {
        return template.queryForObject("SELECT a.id, a.username, a.account, a.password, a.avatar, a.gender, a.age, a.email, a.status, r.id AS roleId, r.role_name AS roleName\n" +
                "FROM administrator AS a\n" +
                "LEFT JOIN administrator_role AS ar ON a.id = ar.administrator_id\n" +
                "LEFT JOIN role AS r ON ar.role_id = r.id where a.id=?", new BeanPropertyRowMapper<>(AdminVo.class), id);
    }

    @Override
    public int update(AdminVo adminVo) {
        try {
            return template.update("update administrator set username=?, account=?, password=?, avatar=?, gender=?, age=?, email=? where id=?",adminVo.getUsername(), adminVo.getAccount(), adminVo.getPassword(), adminVo.getAvatar(), adminVo.getGender(), adminVo.getAge(), adminVo.getEmail(), adminVo.getId());
        } catch (DuplicateKeyException e) {
            return 0;
        }
    }

    @Override
    public void deleteById(Integer id) {
        template.update("delete from administrator where id=?", id);
    }

    @Override
    public void updateStatus(Integer id, Integer status) {
        template.update("update administrator set status=? where id=?", status, id);
    }

    @Override
    public void addRole(AdminVo adminVo) {
        template.update("INSERT INTO administrator_role (administrator_id, role_id) VALUES ((SELECT id FROM administrator WHERE username = ?), ?)"
                    , adminVo.getUsername(), adminVo.getRoleId());
    }


}
